(ns mttm.tools.createdb
  (:use [korma.db]
        [korma.core])
  (:require [lobos.core :as lc]
            [lobos.connectivity :as lcon]
            [lobos.schema :as ls]
            [mttm.config.dbconfig :as conf]))


(defn drop-table [table] (try (lc/drop (ls/table table)) (catch Exception e (prn (str "WARN: " table " not exists")))))

(defn drop-tables []
  (do
    (drop-table :roles)
    (drop-table :users)
    (drop-table :userroles)
    (drop-table :companies)
    ))



(defn create-tables []
  (do
    (lc/create (ls/table :roles (ls/integer :id :primary-key :auto-inc )
                 (ls/varchar :name 100 :not-null )))

    (lc/create (ls/table :companies (ls/integer :id :primary-key :auto-inc )
                 (ls/varchar :name 100 :not-null )))
    (lc/create
      (ls/table :users (ls/integer :id :primary-key :auto-inc )
        (ls/integer :company )
        (ls/varchar :firstname 100)
        (ls/varchar :lastname 100)
        (ls/varchar :username 100 :unique :not-null )
        (ls/varchar :password 100)
        (ls/varchar :sex 1)
        (ls/column :company [:refer :companies :id])
        ))


    ;;todo add PK and FK
    (lc/create (ls/table :userroles
                 (ls/integer :userid)
                 (ls/integer :roleid)
                 (ls/primary-key (ls/table :userroles)  [:userid :roleid])
                 (ls/unique (ls/table :userroles)  [:userid :roleid])
                 (ls/column :userid [:refer :users :id])
                 (ls/column :roleid [:refer :roles :id])
                 ))

    ))

(defn pupulate-test-data []
  (do
    (defentity users)
    (defentity companies)
    (defentity roles)
    (defentity userroles)
    (insert companies (values [{:name "Company 1"}
                               {:name "Company 2"}
                               {:name "Company 3"}
                               {:name "Company 4"}]))

    (insert roles (values     [{:name "User"}
                               {:name "Company admin"}
                               {:name "Company owner"}
                               {:name "Super admin"}]))

    (insert users (values [{:firstname "firstname1" :lastname "lastname1" :username "username1" :sex "M" :password "123" :company 1}
                           {:firstname "firstname2" :lastname "lastname2" :username "username2" :sex "F" :password "123" :company 1}
                           {:firstname "firstname3" :lastname "lastname3" :username "username3" :sex "M" :password "123" :company 1}
                           {:firstname "firstname4" :lastname "lastname4" :username "username4" :sex "F" :password "123" :company 2}
                           {:firstname "firstname5" :lastname "lastname5" :username "username5" :sex "M" :password "123" :company 3}
                           {:firstname "nocompany" :lastname "lastname6" :username "username6" :sex "F" :password "123"}]))

    (insert userroles (values [{:userid 1 :roleid 1}
                                   {:userid 1 :roleid 2}
                                   {:userid 1 :roleid 3}
                                   {:userid 2 :roleid 4}
                                   {:userid 4 :roleid 1}
                                   {:userid 5 :roleid 1}]))
    ))



(defn testdb [] (do
                  (lcon/open-global conf/connection)
                  (defdb prod conf/connection)
                  (drop-tables)
                  (create-tables)
                  (pupulate-test-data)))

(testdb)